package com.xpgk.mvc.budgetMonth;

import java.util.List;

import com.jfinal.log.Log;
import com.xpgk.annotation.Table;
import com.xpgk.mvc.base.BaseModel;
import com.xpgk.mvc.bentryRegister.Customer;
import com.xpgk.mvc.budgetYear.Budget;
import com.xpgk.mvc.organization.Organization;
import com.xpgk.mvc.productSet.Product;

/**
 * 预算记录表
 */
@SuppressWarnings("unused")
@Table(tableName = BudgetMonth.table_name)
public class BudgetMonth extends  BaseModel<BudgetMonth>{

	/**
	 * 
	 */
	private static final long serialVersionUID = 5959487334022891340L;
	
	private static final Log log = Log.getLog(BudgetMonth.class);

	public static final BudgetMonth dao = new BudgetMonth().dao();

	
	/**
	 * 表名称
	 */
	public static final String table_name = "sb_shop_month_budget";
	/**
	 * 字段描述：主键 字段类型：character varying 长度：32
	 */
	public static final String column_ids = "ids";
	/**
	 * 字段描述：机构ids 字段类型：character varying 长度：32
	 */
	public static final String column_organIds = "organIds";
	/**
	 * 字段描述：年月份 字段类型：interesting 长度：2
	 */
	public static final String column_yearmonth = "yearmonth";

	/**
	 * 字段描述：提交预算用户ids 字段类型：character varying 长度：32
	 */
	public static final String column_submitUserIds = "submitUserIds";
	
	/**
	 * 字段描述：提交预算时间 字段类型：date
	 */
	public static final String column_submitDate = "submitDate";
	/**
	 * 字段描述：预算类型(0表示预算没有变更1表示预算变更) 字段类型：character varying 长度：1
	 */
	public static final String column_type = "type";
	/**
	 * 字段描述：更改预算用户ids 字段类型：character varying 长度：32
	 */
	public static final String column_changeUserIds = "changeUserIds";
	/**
	 * 字段描述：更改预算时间  字段类型：character varying 长度：32
	 */
	public static final String column_changeDate = "changeDate";
	/**
	 * 字段描述：审核人ids 字段类型：character varying 长度：32
	 */
	public static final String column_check_userIds = "check_userIds";
	/**
	 * 字段描述：审核是否通过（1表示通过0表示未通过）字段类型：date 
	 */
	public static final String column_check_state = "check_state";
	/**
	 * 字段描述：审核人时间 字段类型：character varying 长度：32
	 */
	public static final String column_check_time = "check_time";
	/**
	 * 字段描述：审核不通过原因 字段类型：character varying 长度：32
	 */
	public static final String column_check_reason = "check_reason";
	/**
	 * 字段描述：提交状态（1表示提交0表示未提交） 字段类型：character varying 长度：32
	 */
	public static final String column_submit_state = "submit_state";
	/**
	 * 字段描述：更新时间 字段类型：date
	 */
	public static final String column_gxsj = "gxsj";
	
	/**
	 * 字段描述：该条数据添加人员is  字段类型：character varying 长度：32
	 */
	public static final String column_createId = "createId";
	/**
	 * 字段描述：该条数据添加时间  字段类型：datetime 长度：0
	 */
	public static final String column_createDate = "createDate";
	/**
	 * 字段描述：该条数据更新人员id  字段类型：character varying 长度：100
	 */
	public static final String column_updateId = "updateId";
	/**
	 * 字段描述：该条数据更新时间  字段类型：datetime 长度：0
	 */
	public static final String column_updateDate = "updateDate";
	
	/**
	 * sqlId : 
	 * 描述：查询疗程某月预算
	 */
	public static final String sqlId_getlcBudgetMonth = "sb.budgetMonth.getlcBudgetMonth";
	/**
	 * sqlId : 
	 * 描述：查询其他产品某月预算
	 */
	public static final String sqlId_getProBudgetMonth = "sb.budgetMonth.getProBudgetMonth";
	
	
	/**根据机构和月份查询月度预算
	 * 
	 * @param organIds
	 * @param yearmonth
	 * @return
	 */
	public  BudgetMonth   getBudgetMonth(String organIds,String yearmonth)
	{
		String  sql="SELECT  * from sb_budgetmonth   WHERE   organIds=?  and  yearmonth=?";
		return  BudgetMonth.dao.findFirst(sql,organIds,yearmonth);
	}
	
	
	/**
	 * 获取新增顾客的除单疗程外的某月的顾客数量和疗程总数
	 * @return
	 */
	public  Customer  getCustomerAndLcnum(String lcIds,String lcUnt,String organIds,String month)
	{
		String sql="SELECT  COUNT(sc.ids) as count,CASE  WHEN  SUM(spca.treatment) is NULL  THEN 0  else SUM(spca.treatment) END as lcsum  FROM  sb_customer sc"
				+" LEFT JOIN (SELECT  sca.customerIds,sca.treatment,sca.time    FROM  sb_customer_agreement sca"
				+" LEFT JOIN sb_product sp ON sp.ids != sca.codeIds WHERE sp.typeIds = ? AND sp.proUnits = ?) spca  on sc.ids=spca.customerIds"
				+" WHERE  sc.organIds=? and sc.registtime  like  ? ";
		return  Customer.dao.findFirst(sql,lcIds,lcUnt,organIds,month);
	}
	/**
	 * 获取其他产品的新增顾客和老顾客的实际数量和金额
	 * @return
	 */
	public  List<Product>  getNumAndPrice(String likemonth,String month,String organIds,String lcIds)
	{
		String  sql="SELECT sp.ids proIds,sp.`name` proName,csal.newnum newnum,csal.oldnum oldnum,csal.newprice newprice,csal.oldprice oldprice FROM  sb_product sp LEFT JOIN (SELECT sps.productID, "
				+" SUM(CASE  WHEN sc.registtime LIKE ? THEN  sps.number  END) newnum,"
				+" SUM(CASE  WHEN sc.registtime < ?  and sps.date like ? THEN  sps.number  END )  oldnum,"
				+" SUM(CASE  WHEN sc.registtime like ? THEN  sps.price   END) newprice,"
				+" SUM(CASE  WHEN sc.registtime < ?  and sps.date like ? THEN  sps.price  END ) oldprice"
				+" FROM  sb_customer sc  LEFT JOIN sb_product_sales_detail sps ON sps.customerID = sc.ids"
				+" WHERE sc.organIds = ? and sps.organids=?  GROUP BY  sps.productID) csal  ON csal.productID = sp.ids"
				+" WHERE sp.typeIds != ?   AND sp.`status` = '1'";
		return   Product.dao.find(sql,likemonth,month,likemonth,likemonth,month,likemonth,organIds,organIds,lcIds);
	}
	
}
